package com.amado.easyui2.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.amado.easyui2.dao.DepartmentDao;
import com.amado.easyui2.dao.impl.StudentDaoImpl.ShopRowMapper;
import com.amado.easyui2.domain.Department;
import com.amado.easyui2.util.DBConnection;
import com.amado.easyui2.vo.Page;
@Repository
public class DepartmetDaoMysqlImpl implements DepartmentDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	@Override
	public int add(Department department) {
		System.out.println(department.toString());
		Connection conn = DBConnection.getConn();
		String sql = "insert into sys_dept(dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name,delete_Flag)values(?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, department.getDepartmentNum());
			ps.setString(i++, department.getDepartmentName());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, department.getCreateName());
			ps.setString(i++, department.getUpdateName());
			ps.setString(i++, department.getDeleteFlag());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int delete(Long id) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		String sql = "update sys_dept set delete_Flag='Y' where dept_ID=?";
//		update from sys_dept t1 set delete_Flag='Y' where not exists
//		
//		 (select * from sys_user where sys_dept = d.sys_dept) 
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setLong(i, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}

	@Override
	public int update(Department department) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
 		String sql = "update sys_dept set dept_Num=?,dept_Name=?,create_Time=?,update_Time=?,create_Name=?,update_Name=? where dept_ID=?";
		try {
			int i = 1;
			ps = conn.prepareStatement(sql);
			ps.setString(i++, department.getDepartmentNum());
			ps.setString(i++, department.getDepartmentName());
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setTimestamp(i++, new java.sql.Timestamp(System.currentTimeMillis()));
			ps.setString(i++, department.getCreateName());
			ps.setString(i++, department.getUpdateName());
			ps.setLong(i++, department.getId());
			return ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {      
			DBConnection.close(null, ps, conn);
		}
		return 0;
	}
	  @Override
	public List<Department> listAll() {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select dept_ID ,dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name from sys_dept where delete_flag='N'";
		List<Department> list = new ArrayList<Department>();
		try {
			ps = conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String departmentName = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(new Department(id, departmentNum, departmentName, createTime, updateTime, createName,
						updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		// list.forEach(e -> {
		// System.out.println(e.toString());
		// });
		return list;
	}
	  
	@Override
	public Department getById(Long id) {
		String sql = "select dept_ID ,dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name from sys_dept where dept_ID=?";
		return getJdbcTemplate().queryForObject(sql, new Long[] { id }, new DeptRowMapper());
	}

	@Override
	public List<Department> listAll(Page page) {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM sys_dept where delete_flag='N' limit ?,?";
		System.out.println("sql"+sql);
		List<Department> list = new ArrayList<Department>();
		try {
			ps = conn.prepareStatement(sql);
			int j=1;
			ps.setInt(j++,(page.getCurrentPage()-1)*page.getRowSize());
			ps.setInt(j++,page.getRowSize());
			rs = ps.executeQuery();
			while (rs.next()) {
				int i = 1;
				Long id = rs.getLong(i++);
				String departmentNum = rs.getString(i++);
				String departmentName = rs.getString(i++);
				Date createTime = rs.getTimestamp(i++);
				Date updateTime = rs.getTimestamp(i++);
				String createName = rs.getString(i++);
				String updateName = rs.getString(i++);
				list.add(new Department(id,departmentNum,departmentName,createTime,updateTime,createName,updateName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return list;
	}

	@Override
	public int listAllCount() {
		Connection conn = DBConnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT count(1) FROM sys_dept where delete_flag='N'";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.close(rs, ps, conn);
		}
		return 0;
	}
	class DeptRowMapper implements RowMapper<Department> {
		// rs为返回结果集，以每行为单位封装着
		@Override
		public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
			//select dept_ID ,dept_Num,dept_Name,create_Time,update_Time,create_Name,update_Name
			Department dept = new Department();
			dept.setId(rs.getLong("dept_id"));
			dept.setDepartmentName(rs.getString("dept_name"));
			dept.setDepartmentNum(rs.getString("dept_num"));
			dept.setCreateTime(rs.getTime("create_time"));
			dept.setUpdateTime(rs.getTime("update_Time"));
			dept.setCreateName(rs.getString("create_Name"));
			dept.setUpdateName(rs.getString("update_Name"));
			return dept;
		}
	}
	@Override
	public Department getByDeptName(String department) {
		String sql="select * from sys_dept where dept_name=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { department }, new DeptRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}

	@Override
	public Department getByDeptNumber(String deptNumber) {
		String sql="select * from sys_dept where dept_num=?"; 
		try {
			// 如果没有查询到数据会报异常EmptyResultDataAccessException
			return getJdbcTemplate().queryForObject(sql, new String[] { deptNumber }, new DeptRowMapper());
		} catch (EmptyResultDataAccessException e) {
			return null;
		}
	}
	
}
